import re
import os
from typing import Dict, Callable, Optional, Union

# MySQL到Hive类型映射
MYSQL_TO_HIVE_TYPE_MAPPING: Dict[str, Union[str, Callable]] = {
    r'TINYINT(\(\d+\))?(\s+UNSIGNED)?': 'TINYINT',
    r'SMALLINT(\(\d+\))?(\s+UNSIGNED)?': 'SMALLINT',
    r'INT(\(\d+\))?(\s+UNSIGNED)?': 'INT',
    r'INTEGER(\(\d+\))?(\s+UNSIGNED)?': 'INT',
    r'BIGINT(\(\d+\))?(\s+UNSIGNED)?': 'BIGINT',
    r'FLOAT(\(\d+,\d+\))?(\s+UNSIGNED)?': 'FLOAT',
    r'DOUBLE(\(\d+,\d+\))?(\s+UNSIGNED)?': 'DOUBLE',
    r'DECIMAL(\(\s*(\d+)\s*,\s*(\d+)\s*\))': lambda m: f"DECIMAL({int(m.group(1))},{int(m.group(2))})",
    r'DATE': 'DATE',
    r'DATETIME': 'TIMESTAMP',
    r'TIMESTAMP(\(\d+\))?': 'TIMESTAMP',
    r'TIME': 'STRING',
    r'YEAR(\(\d+\))?': 'SMALLINT',
    r'CHAR\(\s*\d+\s*\)': 'STRING',
    r'VARCHAR\(\s*\d+\s*\)': 'STRING',
    r'TINYTEXT': 'STRING',
    r'TEXT': 'STRING',
    r'MEDIUMTEXT': 'STRING',
    r'LONGTEXT': 'STRING',
    r'BINARY\(\s*\d+\s*\)': 'BINARY',
    r'VARBINARY\(\s*\d+\s*\)': 'BINARY',
    r'TINYBLOB': 'BINARY',
    r'BLOB': 'BINARY',
    r'MEDIUMBLOB': 'BINARY',
    r'LONGBLOB': 'BINARY',
    r'ENUM\([^)]+\)': 'STRING',
    r'SET\([^)]+\)': 'ARRAY<STRING>',
    r'JSON': 'STRING',
    r'GEOMETRY': 'BINARY'
}


def map_mysql_type_to_hive(mysql_type: str) -> str:
    """将MySQL数据类型映射到Hive数据类型"""
    mysql_type = mysql_type.upper().strip()

    for pattern, hive_type in MYSQL_TO_HIVE_TYPE_MAPPING.items():
        compiled_pattern = re.compile(pattern, re.IGNORECASE)
        match = compiled_pattern.fullmatch(mysql_type)
        if match:
            if callable(hive_type):
                return hive_type(match)
            return hive_type

    return 'STRING'


def parse_mysql_create_table(sql: str) -> Dict:
    """解析MySQL的CREATE TABLE语句"""
    # 提取表名
    table_name_match = re.search(r'CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?`?([^\s`]+)`?', sql, re.IGNORECASE)
    if not table_name_match:
        raise ValueError("无法从SQL中提取表名")
    table_name = table_name_match.group(1)

    # 提取字段定义部分
    columns_section_match = re.search(r'\((.*)\)', sql, re.DOTALL)
    if not columns_section_match:
        raise ValueError("无法从SQL中提取字段定义部分")
    columns_section = columns_section_match.group(1)

    # 解析各个字段
    columns = []
    column_defs = re.split(r',\s*(?![^()]*\))', columns_section.strip())

    for column_def in column_defs:
        column_def = column_def.strip()
        if not column_def or column_def.startswith(('PRIMARY KEY', 'KEY', 'UNIQUE', 'INDEX')):
            continue

        # 提取字段名和类型
        column_match = re.match(r'`?([^`\s]+)`?\s+([^\s,]+)', column_def)
        if column_match:
            column_name = column_match.group(1)
            mysql_type = column_match.group(2)

            # 检查是否有注释
            comment_match = re.search(r'COMMENT\s+\'([^\']*)\'', column_def, re.IGNORECASE)
            comment = comment_match.group(1) if comment_match else None

            columns.append({
                'name': column_name,
                'mysql_type': mysql_type,
                'comment': comment
            })

    # 提取表注释
    table_comment_match = re.search(r'COMMENT\s*=\s*\'([^\']*)\'', sql, re.IGNORECASE)
    table_comment = table_comment_match.group(1) if table_comment_match else None

    return {
        'table_name': table_name,
        'columns': columns,
        'table_comment': table_comment
    }


def generate_hive_create_table(table_info: Dict) -> str:
    """生成Hive CREATE TABLE语句"""
    table_name = table_info['table_name']
    columns = table_info['columns']
    table_comment = table_info.get('table_comment')

    # 生成字段定义部分
    column_defs = []
    for column in columns:
        hive_type = map_mysql_type_to_hive(column['mysql_type'])
        column_def = f"`{column['name']}` {hive_type}"
        if column.get('comment'):
            column_def += f" COMMENT '{column['comment']}'"
        column_defs.append(column_def)

    # 构建完整的CREATE TABLE语句
    hive_sql = f"CREATE EXTERNAL TABLE IF NOT EXISTS `{table_name}` (\n"
    hive_sql += ",\n".join(f"  {col_def}" for col_def in column_defs)
    hive_sql += "\n)"

    if table_comment:
        hive_sql += f"\nCOMMENT '{table_comment}'"

    hive_sql += "\nROW FORMAT DELIMITED\nFIELDS TERMINATED BY '\\t'\nSTORED AS TEXTFILE"
    # 添加location
    hive_sql += f"\nLOCATION '/dw/hive/jms_ods.db/external/{table_name}'"
    # 添加tbl_properties
    hive_sql += f"\nTBLPROPERTIES ('auto.create.table'='true', 'auto.evolve.column.type'='true', 'auto.create.db'='true', 'auto.convert.join'='true', 'transactional'='true', 'transactional_properties'='insert_only')"
    hive_sql += "\nROW FORMAT DELIMITED\nFIELDS TERMINATED BY '\\t'\nSTORED AS TEXTFILE"

    return hive_sql


def convert_mysql_to_hive(input_file: str, output_file: str):
    """
    将MySQL建表语句文件转换为Hive建表语句文件
    :param input_file: MySQL建表语句文件路径
    :param output_file: 输出Hive建表语句文件路径
    """
    # 检查输入文件是否存在
    if not os.path.exists(input_file):
        raise FileNotFoundError(f"输入文件不存在: {input_file}")

    # 确保输出目录存在
    output_dir = os.path.dirname(output_file)
    if output_dir and not os.path.exists(output_dir):
        try:
            os.makedirs(output_dir)
        except PermissionError:
            raise PermissionError(f"没有权限创建输出目录: {output_dir}")

    # 检查输出目录是否可写
    if output_dir and not os.access(output_dir, os.W_OK):
        raise PermissionError(f"没有写入权限到目录: {output_dir}")

    with open(input_file, 'r', encoding='utf-8') as f:
        mysql_sql = f.read()

    # 移除注释和多余的空格/换行
    mysql_sql = re.sub(r'--.*?\n', '', mysql_sql)
    mysql_sql = re.sub(r'/\*.*?\*/', '', mysql_sql, flags=re.DOTALL)
    mysql_sql = re.sub(r'\s+', ' ', mysql_sql).strip()

    table_info = parse_mysql_create_table(mysql_sql)
    hive_sql = generate_hive_create_table(table_info)

    print('hive-hql:{}'.format(hive_sql))

    try:
        with open(output_file, 'w', encoding='utf-8') as f:
            f.write(hive_sql)
        print(f"转换完成，结果已保存到 {output_file}")
    except PermissionError:
        # 尝试在用户桌面创建文件
        desktop = os.path.join(os.path.expanduser("~"), "Desktop")
        fallback_output = os.path.join(desktop, os.path.basename(output_file))
        with open(fallback_output, 'w', encoding='utf-8') as f:
            f.write(hive_sql)
        print(f"原始输出路径无权限，文件已保存到桌面: {fallback_output}")


if __name__ == '__main__':
    # 直接在这里指定输入输出文件路径
    input_path = 'D:/projects/yl_procject/yl-bgdm-airflow/src/kumiler_platform/ddl_create_script/source_table_ddl/spmi_pdd_bill.sql'
    output_path = 'D:/projects/yl_procject/yl-bgdm-airflow/src/kumiler_platform/hive_output/spmi_pdd_bill.hql'


    # 或者使用交互式输入
    # input_path = input("请输入MySQL建表语句文件路径: ").strip()
    # output_path = input("请输入Hive建表语句输出路径: ").strip()

    try:
        convert_mysql_to_hive(input_path, output_path)
    except Exception as e:
        print(f"发生错误: {str(e)}")
        print("建议:")
        print("1. 检查输入文件路径是否正确")
        print("2. 确保输出路径有写入权限")
        print("3. 尝试将输出路径改为您的桌面或文档目录")
